import pymysql

class MyMysql(object):
    def __init__(self):
        self.conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='123456', db='798', charset='utf8')
        self.cursor = self.conn.cursor()

    def __del__(self):
        self.conn.close()

    def create_table(self):
        """创建表"""
        sql1 = """CREATE TABLE IF NOT EXISTS users(
                    u_id INT  PRIMARY KEY AUTO_INCREMENT,
                    name VARCHAR(20) NOT NULL,
                    password VARCHAR(20) NOT NULL,
                    phone VARCHAR(20) NOT NULL,
                    is_admin BOOLEAN DEFAULT FALSE,
                    register_date DATETIME DEFAULT NULL,
                    card_number VARCHAR(20) DEFAULT NULL,
                    points INT DEFAULT 0,
                    last_login_date DATETIME DEFAULT NULL,
                    is_deleted BOOLEAN DEFAULT False
                    );
                """
        sql2 = """CREATE TABLE IF NOT EXISTS device(
                    d_id INT PRIMARY KEY AUTO_INCREMENT,
                    category_name VARCHAR(20) NOT NULL,
                    install_location VARCHAR(100) NOT NULL,
                    qr_code VARCHAR(255) NOT NULL,
                    is_functional BOOLEAN DEFAULT True
                    );
                """

        sql3 = """CREATE TABLE IF NOT EXISTS orders(
                    order_id INT PRIMARY KEY AUTO_INCREMENT,
                    user_id INT NOT NULL,
                    device_id INT NOT NULL,
                    consume_time DATETIME NOT NULL,
                    consume_amount DECIMAL(10,2) NOT NULL,
                    payment_method ENUM('card','alipay') NOT NULL,
                    FOREIGN KEY(user_id) REFERENCES users(u_id),
                    FOREIGN KEY(device_id) REFERENCES device(d_id)
                     );
                """

        try:
            sql = [sql1, sql2, sql3]
            for i in sql:
                self.cursor.execute(i)
                self.conn.commit()
        except Exception as e:
            print("创建表失败，原因是%s" % e)


    def select_all_user(self):
        """查看用户"""
        sql = "SELECT * FROM users where is_admin = 0;"
        try:
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            return result
        except Exception as e:
            print("查询表失败，原因是%s" % e)
    def insert_one_user(self, user):
        """添加新用户"""
        sql = "insert into users(name,password,phone,is_admin,register_date) values(%s,%s,%s,%s,%s)"
        try:
            self.cursor.execute(sql, user)
            self.conn.commit()
        except Exception as e:
            print("插入表失败，原因是%s" % e)

    def select_all_device(self):
        """查看设备"""
        sql = "SELECT * FROM device;"
        try:
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            return result
        except Exception as e:
            print("查询表失败，原因是%s" % e)

    def select_all_order(self):
        """查看订单"""
        sql = "SELECT * FROM orders;"
        try:
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            return result
        except Exception as e:
            print("查询表失败，原因是%s" % e)

    def delete_user(self, u_id):
        """删除用户"""
        sql = "DELETE FROM users WHERE u_id = %s;"
        try:
            self.cursor.execute(sql, u_id)
            self.conn.commit()
        except Exception as e:
            print("删除表失败，原因是%s" % e)

    def delete_equip(self, d_id):
        """删除设备"""
        sql = "DELETE FROM device WHERE d_id = %s;"
        try:
            self.cursor.execute(sql, d_id)
            self.conn.commit()
        except Exception as e:
            print("删除表失败，原因是%s" % e)

    def delete_order(self, o_id):
        """删除订单"""
        sql = "DELETE FROM orders WHERE order_id = %s;"
        try:
            self.cursor.execute(sql, o_id)
            self.conn.commit()
        except Exception as e:
            print("删除表失败，原因是%s" % e)

    def select_month(self):
        """查询月度收入情况"""
        sql = "SELECT MONTH(consume_time) AS m FROM orders GROUP BY m;"
        try:
            mon = []
            self.cursor.execute(sql)
            results = self.cursor.fetchall()
            for i in results:
                mon.append(i[0])
            return mon

        except Exception as e:
            print("查询月失败，原因是%s" % e)

    def select_month_income(self):
        """查询月度收入情况"""
        sql = "SELECT SUM(consume_amount) AS total_income FROM orders GROUP BY MONTH(consume_time);"
        try:
            m_incomes = []
            self.cursor.execute(sql)
            results = self.cursor.fetchall()
            for i in results:
                m_incomes.append(i[0])
            return m_incomes
        except Exception as e:
            print("查询月度收入失败，原因是%s" % e)


    def month_equip_incomes(self, m):
        """设备收入占比"""
        sql = "SELECT device.category_name,sum(orders.consume_amount) FROM device LEFT JOIN orders ON device.d_id=orders.device_id WHERE MONTH(orders.consume_time)=%s GROUP BY device.category_name;"
        try:
            self.cursor.execute(sql, m)
            results = self.cursor.fetchall()
            return results
        except Exception as e:
            print("查询设备失败，原因是%s" % e)

    def location_query(self, m):
        """设备所在区域占比"""
        sql = "SELECT device.install_location,sum(orders.consume_amount) FROM device LEFT JOIN orders ON device.d_id=orders.device_id WHERE MONTH(orders.consume_time)=%s GROUP BY device.install_location;"
        try:
            self.cursor.execute(sql, m)
            results = self.cursor.fetchall()
            print(results)
            return results
        except Exception as e:
            print("查询表失败，原因是%s" % e)




if __name__ == '__main__':
    mysql = MyMysql()
    # mysql.month_equip_incomes()
    # mysql.create_table()

